﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using POSPlus.QueryCreator;

namespace POSPlus.EZBarTender
{
    public class WorkTableControl
    {
        private SqlConnection _sqlConnection;
        private string _sqlConnectionString;
        private SqlCommand _sqlCommand;

        public WorkTableControl(string connectionString)
        {
            _sqlConnectionString = connectionString;
        }

        public bool CreateTable(string tableName, string[] columnNames, string[] dataTypes)
        {
            if (columnNames.Length == dataTypes.Length)
            {
                string command = "IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" +
                    tableName + "') CREATE TABLE " + tableName + " (";

                for (int i = 0; i < columnNames.Length; i++)
                {
                    command += columnNames[i] + " " + dataTypes[i] + ", ";
                }

                command = command.Substring(0, command.LastIndexOf(','));
                command += ")";

                _sqlConnection = new SqlConnection(_sqlConnectionString);
                _sqlCommand = new SqlCommand(command, _sqlConnection);


                try
                {
                    _sqlConnection.Open();
                    int rowsAffected = _sqlCommand.ExecuteNonQuery();
                    _sqlConnection.Close();
                    _sqlConnection.Dispose();
                    _sqlCommand.Dispose();
                    return true;
                }
                catch (SqlException ex)
                {
                    DebugBox.Show("SQL Statement: " + command + "\n" + ex.Message);
                    _sqlConnection.Close();
                    _sqlConnection.Dispose();
                    _sqlCommand.Dispose();
                    return false;
                }
            }
            else
                return false;
        }

        public bool InsertRecord(string tableName, string[] columns, Query query)
        {
            string command = "INSERT INTO " + tableName + " (";
            for (int i = 0; i < columns.Length; i++)
            {
                command += columns[i] + ", ";
            }
            command = command.Substring(0, command.LastIndexOf(','));
            command += ") " + query;

            _sqlConnection = new SqlConnection(_sqlConnectionString);
            _sqlCommand = new SqlCommand(command, _sqlConnection);

            try
            {
                _sqlConnection.Open();
                int rowsAffected = _sqlCommand.ExecuteNonQuery();

                command = "UPDATE " + tableName + " SET TagType = 'reg' " + query.WHERE.ToString();
                _sqlCommand = new SqlCommand(command, _sqlConnection);
                _sqlCommand.ExecuteNonQuery();

                _sqlConnection.Close();
                _sqlConnection.Dispose();
                _sqlCommand.Dispose();
                return true;
            }
            catch(SqlException ex)
            {
                DebugBox.Show("SQL Statement: " + command + "\n" + ex.Message);
                return false;
            }
        }
        public bool InsertRecord(string tableName, string[] columns, string[] values)
        {
            string command = "INSERT INTO " + tableName + " (";
            for (int i = 0; i < columns.Length; i++)
            {
                command += columns[i] + ", ";
            }
            command = command.Substring(0, command.LastIndexOf(','));
            command += " VALUES (";
            for (int i = 0; i < values.Length; i++)
            {
                command += values[i] + ",";
            }
            command = command.Substring(0, command.LastIndexOf(','));
            command += ")";

            _sqlConnection = new SqlConnection(_sqlConnectionString);
            _sqlCommand = new SqlCommand(command, _sqlConnection);

            try
            {
                _sqlConnection.Open();
                int rowsAffected = _sqlCommand.ExecuteNonQuery();
                _sqlConnection.Close();
                _sqlConnection.Dispose();
                _sqlCommand.Dispose();
                return true;
            }
            catch(SqlException ex)
            {
                DebugBox.Show("SQL Statement: " + command + "\n" + ex.Message);
                return false;
            }
        }

        public bool DropTable(string tablename)
        {
            string command = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + 
                tablename + "') DROP TABLE " + tablename;

            _sqlConnection = new SqlConnection(_sqlConnectionString);
            _sqlCommand = new SqlCommand(command, _sqlConnection);

            try
            {
                _sqlConnection.Open();
                int rowsAffected = _sqlCommand.ExecuteNonQuery();
                _sqlConnection.Close();
                _sqlConnection.Dispose();
                _sqlCommand.Dispose();
                return true;
            }
            catch (SqlException ex)
            {
                DebugBox.Show("SQL Statement: " + command + "\n" + ex.Message);
                return false;
            }
        }

        #region Getters and Setters
        public SqlConnection Connection
        {
            get { return _sqlConnection; }
        }

        public string ConnectionString
        {
            get { return _sqlConnectionString; }
            set { _sqlConnectionString = value; }
        }

        public SqlCommand Command
        {
            get { return _sqlCommand; }
            set { _sqlCommand = value; }
        }
        #endregion
    }
}